﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Data.Mobile
{
    public class DataLayer : IDisposable
    {
        SqlConnection sqlConn;
        SqlCommand sqlCmd;
        SqlTransaction sqlTrans;

        string m_ServerName = "";
        string m_DatabaseName = "";
        string m_UserName = "";
        string m_Password = "";

        private string StrConn
        {
            get {
                return string.Format("User ID={0};pwd={1};data source={2};persist security info=False;initial catalog={3}",
                            m_UserName, m_Password, m_ServerName, m_DatabaseName);
            }
        }

        public void Dispose()
        {
            try {sqlTrans.Dispose(); }catch { }
            try { sqlCmd.Dispose(); }catch { }
            try { sqlConn.Close(); }catch { }finally { sqlConn.Dispose(); }
        }

        public DataLayer(string ServerName, string DatabaseName, string UserName, string Password)
        {
            m_ServerName = ServerName;
            m_DatabaseName = DatabaseName;
            m_UserName = UserName;
            m_Password = Password;

            sqlConn = new SqlConnection(this.StrConn);
        }

        private void Open()
        {
            if (sqlConn == null)
                sqlConn = new SqlConnection(this.StrConn);
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
        }

        public object Scalar(string sql, string ParamNames, params object[] ParamValues)
        {
            Open();

            sqlCmd = new SqlCommand(sql, sqlConn);
            sqlCmd.CommandType = CommandType.Text;
            AssignParams(ParamNames, ParamValues);

            return sqlCmd.ExecuteScalar();
        }

        public DataTable Exec(string Name, string Params, params object[] Values)
        {
            Open();
            
            System.Data.SqlClient.SqlDataAdapter sqlDA;
            System.Data.DataTable DTT = new System.Data.DataTable("table");

            sqlCmd = new System.Data.SqlClient.SqlCommand(Name, sqlConn);

            sqlCmd.CommandText = Name;
            sqlCmd.CommandType = CommandType.StoredProcedure;

            AssignParams(Params, Values);

            sqlDA = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
            sqlDA.Fill(DTT);
            
            return DTT;
        }

        private void AssignParams(string Params, params object[] Values)
        {
            sqlCmd.Parameters.Clear();
            string[] arr = Params.Split(',');
            if (!object.Equals(arr, null) && arr.Length > 0)
            {
                for (int i = 0; i < arr.Length; i++)
                    sqlCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(arr[i], Values[i]));
            }
        }

    }
}
